
// Copyright (c) 2002 HTHK
// Fix for cursor exceed
package com.ffpc.sql;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.apache.commons.configuration.Configuration;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * <p>A pooled connection provider based on the legacy DriverManager API.</p>
 * 
 * <p>Supported configuration keys (prefix omitted for clarity):</p>
 * 
 * <table border="1">
 * <tr>
 * <th>Key</th>
 * <th>Description</th>
 * <th>Required?</th>
 * <th>Default</th>
 * </tr>
 * <tr>
 * <td>driver</td>
 * <td>JDBC driver class</td>
 * <td align="center">Yes</td>
 * <td align="center">n/a</td>
 * </tr>
 * <tr>
 * <td>url</td>
 * <td>Database URL</td>
 * <td align="center">Yes</td>
 * <td align="center">n/a</td>
 * </tr>
 * <tr>
 * <td>username</td>
 * <td>Database user name</td>
 * <td align="center">Yes</td>
 * <td align="center">n/a</td>
 * </tr>
 * <tr>
 * <td>password</td>
 * <td>Database user password</td>
 * <td align="center">Yes</td>
 * <td align="center">n/a</td>
 * </tr>
 * <tr>
 * <td>initialConnections</td>
 * <td>Initial number of connections. Set to 0 to avoid any connection from being established at initialization.</td>
 * <td align="center">No</td>
 * <td align="center">1</td>
 * </tr>
 * <tr>
 * <td>maxConnections</td>
 * <td>Maximum no. of available connections.</td>
 * <td align="center">No</td>
 * <td align="center">10</td>
 * </tr>
 * <tr>
 * <td>waitIfBusy</td>
 * <td>Wait if all available connection is being used. If set to false, SQLException is thrown immediately.</td>
 * <td align="center">No</td>
 * <td align="center">true</td>
 * </tr>
 * <tr>
 * <td>maxWait</td>
 * <td>Maximum no. of seconds to wait for a connection. If no connection is available after the said period, 
 * SQLException is thrown. If set to 0, a thread will wait indefinitely for a connection.</td>
 * <td align="center">No</td>
 * <td align="center">60</td>
 * </tr>
 * </table>
 * 
 * @author KENNETHLCH
 * @version $Id: ConnectionPool.java,v 1.1.1.1 2013/08/16 05:35:48 tengyang Exp $
 */
public class ConnectionPool implements Runnable, ConnectionProvider {
    
    private static final int DEFAULT_INIT_CXN = 1;
    private static final int DEFAULT_MAX_CXN = 10;
    private static final int DEFAULT_MAX_WAIT = 60;     //in secs

    private Logger logger;
    
    private String name, driver, url, username, password;
    private int initialConnections;
    private int maxConnections;
    private boolean waitIfBusy;
    private long maxWait;
    
    private Vector availableConnections;
    private Vector leasedConnections;
    private List waitList;

    private boolean connectionPending = false;
  
    public ConnectionPool() {
        
    }
  
  /**
   * Writes the status of this connection pool to the given stream.
   * 
   * @param out A character stream.
   */
  @Override
public void diagnose(PrintWriter out) {
      out.println("------------------------------------------------------------");
      out.println(this);
      out.println("------------------------------------------------------------");

      if (leasedConnections.isEmpty()) {
          out.println("No outstanding lease.");
          out.println();
      }
      
      for (Iterator j = leasedConnections.iterator(); j.hasNext(); ) {
          PooledConnection conn = (PooledConnection) j.next();
          out.println(conn);
          out.println("Stack trace: " + conn.getStackTrace());
          out.println();
      }
  }
  
  @Override
public Connection getConnection(ConnectionFactory factory) throws SQLException {
      PooledConnection conn = (PooledConnection) internalGetConnection();
      logger.trace("conn=" + conn);
      if (!isAlive(conn)) {
          synchronized (this) {
              leasedConnections.removeElement(conn);
              notifyAll();
          }
          close(conn);
          conn = (PooledConnection) getConnection(factory);
      }
      conn.setConnectionFactory(factory);
      return conn;
  }
  
  private synchronized Connection internalGetConnection()
      throws SQLException {
    if (!availableConnections.isEmpty()) {
        // Take the first, least recently used (LRU), connection from the pool
        // instead of the last one. This facilitates fair use of the idle connections, 
        // and lowers the average idle time.
      PooledConnection existingConnection =
        (PooledConnection)availableConnections.remove(0);

      // If connection on available list is closed (e.g.,
      // it timed out), then remove it from available list
      // and repeat the process of obtaining a connection.
      // Also wake up threads that were waiting for a
      // connection because maxConnection limit was reached.
      if (existingConnection.isClosed()) {
          close(existingConnection);
        notifyAll(); // Freed up a spot for anybody waiting
        return(internalGetConnection());
      } else {
        leasedConnections.add(existingConnection);
        existingConnection.markLeased();
        return(existingConnection);
      }
    } else {
      
      // Three possible cases:
      // 1) You haven't reached maxConnections limit. So
      //    establish one in the background if there isn't
      //    already one pending, then wait for
      //    the next available connection (whether or not
      //    it was the newly established one).
      // 2) You reached maxConnections limit and waitIfBusy
      //    flag is false. Throw SQLException in such a case.
      // 3) You reached maxConnections limit and waitIfBusy
      //    flag is true. Then do the same thing as in second
      //    part of step 1: wait for next available connection.
      
      if (totalConnections() < maxConnections) {
        if (!connectionPending) {
            makeBackgroundConnection();
        } else {
            logger.info("Connection is being established in the background");
        }
      } else if (!waitIfBusy) {
        throw new SQLException("Connection limit reached");
      }
      // Wait for either a new connection to be established
      // (if you called makeBackgroundConnection) or for
      // an existing connection to be freed up.
      try {
          // Avoid waiting indefinitely.
          long t = System.currentTimeMillis(), elapsed = 0;
          
          registerThread();
          wait(maxWait);
          elapsed = System.currentTimeMillis() - t;
          
          if (elapsed >= maxWait) {
              SQLException sqle = new SQLException("Timed out waiting for a connection. Please try again later.");
              logger.error("", sqle);
              throw sqle;
          }

      } catch(InterruptedException ie) {
          SQLException sqle = new SQLException("Wait aborted due to connection pool shutdown.");
          logger.error("", sqle);
          throw sqle;
      } finally {
          unregisterThread();
      }
      // Someone freed up a connection, so try again.
      return(internalGetConnection());
    }
  }
  
  private synchronized void registerThread() {
      waitList.add(Thread.currentThread());
  }
  
  private synchronized void unregisterThread() {
      waitList.remove(Thread.currentThread());
  }

  // You can't just make a new connection in the foreground
  // when none are available, since this can take several
  // seconds with a slow network connection. Instead,
  // start a thread that establishes a new connection,
  // then wait. You get woken up either when the new connection
  // is established or if someone finishes with an existing
  // connection.
  
  private static long bgThreadId = 0;

  private void makeBackgroundConnection() {
    try {
      Thread connectThread = new Thread(this, "ConnectionPool-" + bgThreadId++);
      connectThread.start();
      connectionPending = true;
    } catch(OutOfMemoryError oome) {
      // Give up on new connection
      logger.error("", oome);
    }
  }

  @Override
public void run() {
    Connection connection = null;

    try {
      connection = makeNewConnection();
    } catch(SQLException e) {
      logger.error("", e);
    }

    synchronized(this) {
      if (connection != null) {
        availableConnections.addElement(connection);
        notifyAll();
      }
      connectionPending = false;
    }
  }

  // This explicitly makes a new connection. Called in
  // the foreground when initializing the ConnectionPool,
  // and called in the background when running.
  
  private Connection makeNewConnection()
      throws SQLException {
    try {
      // Load database driver if not already loaded
      Class.forName(driver);
      // Establish network connection to database
      Connection connection =
        DriverManager.getConnection(url, username, password);
      return new PooledConnection(connection, this);
    } catch(ClassNotFoundException cnfe) {
      // Simplify try/catch blocks of people using this by
      // throwing only one exception type.
      throw new SQLException("Can't find class for driver: " +
                             driver);
    }
  }
  
  synchronized void release(Connection connection) {
      leasedConnections.removeElement(connection);
      availableConnections.addElement(connection);
      // Wake up threads that are waiting for a connection
      notifyAll(); 
  }
  
  public synchronized void free(Connection connection) {
    try {
        connection.close();
    } catch (SQLException e) {
        // No exception would be thrown
    }
  }
    
  public synchronized int totalConnections() {
    return(availableConnections.size() +
           leasedConnections.size());
  }
  
  private synchronized void interruptWaiters() {
      for (Iterator i = waitList.iterator(); i.hasNext(); ) {
          Thread t = (Thread) i.next();
          t.interrupt();
      }
  }

  /** Close all the connections. Use with caution:
   *  be sure no connections are in use before
   *  calling. Note that you are not <I>required</I> to
   *  call this when done with a ConnectionPool, since
   *  connections are guaranteed to be closed when
   *  garbage collected. But this method gives more control
   *  regarding when the connections are closed.
   */

  public synchronized void closeAllConnections() {
      if (availableConnections.isEmpty() && leasedConnections.isEmpty())
          return;

    logger.info(name + ": Closing all connections...");
    
    closeConnections(availableConnections);
    closeConnections(leasedConnections);
    
    availableConnections.clear();
    leasedConnections.clear();

    logger.info(name + ": All connections closed.");
    
    // Interrupt all threads that are still waiting.
    interruptWaiters();

  }
  
  private void closeConnections(Collection connections) {
      for (Iterator i = connections.iterator(); i.hasNext(); ) {
          PooledConnection conn = (PooledConnection) i.next();
          close(conn);
      }
  }
  
  private void close(PooledConnection conn) {
      try {
          if (conn != null && !conn.isClosed())
              conn.hardClose();
      } catch (SQLException e) {
          logger.debug(e);
      }
  }

  /**
   * This method is no longer synchronized so that pool state can be obtained even 
   * if its locked by another thread. Note however that this would introduce little
   * inaccuracy if this method is called when another thread is in the middle of 
   * getConnection().
   */
  @Override
public String toString() {
    String info =
        super.toString() +
      "[" + name + "] " +
      " url=" + url +
      ", username=" + username +
      ", max=" + maxConnections +
      ", free=" + availableConnections.size() +
      ", busy=" + leasedConnections.size() +
      ", wait=" + waitList.size() +
      ", waitIfBusy=" + waitIfBusy +
      ", maxWait=" + maxWait;
    return(info);
  }

  @Override
protected void finalize()
    throws Throwable
  {
      this.closeAllConnections();
  }
  
  private boolean isAlive(Connection con) {
      PreparedStatement stmt = null;
      ResultSet rs = null;
      java.sql.DatabaseMetaData dm = null;

      try{
          dm = con.getMetaData();
      }catch(SQLException e){
          logger.warn("Could not obtain database metadata", e);
          // Can't determine status, assume it is alive.
          return true;
      }

      try {
          if (dm.getDatabaseProductName().indexOf("Microsoft") >= 0){
              stmt = con.prepareStatement("SELECT getdate()");
          }else{
              stmt = con.prepareStatement("SELECT 1 FROM DUAL");
          }
          rs = stmt.executeQuery();
          // If the query does not generate exception then the connection is alive.
          return true;
      } catch (Exception e) {
          logger.warn("Alive check failed", e);
          return false;
      } finally {
          try {
              if (rs != null) rs.close();
              if (stmt != null) stmt.close();
          } catch (SQLException e) {}
      }
  }
  
    @Override
	public String getName() {
        return name;
    }
    
    public String getDriver() {
        return driver;
    }
    
    public String getUrl() {
        return url;
    }
    
    public String getUsername() {
        return username;
    }
    
    public int getMaxConnections() {
        return maxConnections;
    }
    
    public boolean isWaitIfBusy() {
        return waitIfBusy;
    }

    @Override
    public void destroy() {
        closeAllConnections();
    }
    
    @Override
    public void init(Configuration config) throws SQLException, DataException {
        name = config.getString("name");
        driver = config.getString("driver");
        url = config.getString("url");
        //sid = config.getString("sid");
        username = config.getString("username");
        password = config.getString("password");
        initialConnections = config.getInt("initialConnections", DEFAULT_INIT_CXN);
        maxConnections = config.getInt("maxConnections", DEFAULT_MAX_CXN);
        waitIfBusy = !("false".equals(config.getString("waitIfBusy")));
        maxWait = 1000L * config.getInt("maxWait", DEFAULT_MAX_WAIT);

        if (driver == null)
            throw new DataException(name + ": JDBC driver is required");

        if (url == null)
            throw new DataException(name + ": Database URL is required");
        
        if (username == null)
            throw new DataException(name + ": Database user name is required");
        
        if (password == null)
            throw new DataException(name + ": Database user password is required");

        if (initialConnections > maxConnections) {
            initialConnections = maxConnections;
        }

        availableConnections = new Vector(initialConnections);
        leasedConnections = new Vector(0);
        waitList = new ArrayList();

        for(int i=0; i<initialConnections; i++) {
          availableConnections.addElement(makeNewConnection());
        }
        
        logger = LogManager.getLogger(this.getClass().getName() + ":" + name);
    }
    
}


